package com.eascs.loan.service.impl;

import com.alibaba.dubbo.config.annotation.Reference;
import com.eascs.admin.o2o.badloan.service.O2oBadLoanService;
import com.eascs.admin.o2o.loan.entity.O2oLoanapplyEntity;
import com.eascs.admin.o2o.loan.service.O2oLoanapplyService;
import com.eascs.app.dao.PageResult;
import com.eascs.app.dao.SqlPageQuery;
import com.eascs.app.log.Logger;
import com.eascs.app.log.LoggerFactory;
import com.eascs.common.util.StringUtil;
import com.eascs.loan.dao.O2oPaymentsDao;
import com.eascs.loan.entity.O2oPaymentsPlan;
import com.eascs.loan.entity.O2oRepaymentEntity;
import com.eascs.loan.form.O2oPaymentsForm;
import com.eascs.loan.service.O2oLoanService;
import com.eascs.loan.service.O2oPaymentsService;
import com.eascs.loan.vo.O2oRepaymentRecordFindVo;
import com.eascs.web.sso.User;
import com.eascs.web.sso.UserHolder;
import com.eascs.workflow.common.entity.wf.ProcessTemplate;
import com.eascs.workflow.common.service.wf.WorkFlowService;
import com.eascs.workflow.common.vo.ModelResult;
import com.eascs.workflow.common.vo.WFRequestParam;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.math.BigDecimal;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

@Service
public class O2oPaymentsServiceImpl implements O2oPaymentsService {

	@Autowired
	O2oPaymentsDao paymentsDao;
	@Autowired
	O2oLoanService o2oLoanService;
	@Autowired
	O2oLoanapplyService o2oLoanapplyService;
	@Reference
	private WorkFlowService   workFlowService;
	@Autowired
	private O2oBadLoanService service;
	@Value("${o2o.admin.repaymentUrl}")
	String startUrl;

	private static Logger log = LoggerFactory.getLogger(O2oPaymentsServiceImpl.class);

	@Override
	public PageResult<Object[]> paymentsList(O2oPaymentsForm vo) {
		SqlPageQuery<Object[]> sqlPageQuery = new SqlPageQuery<>(Object[].class, vo.getPageSize(), vo.getShowPage());
		StringBuilder sql = new StringBuilder();
		SimpleDateFormat s = new SimpleDateFormat("yyyy-MM-dd");
		sql.append(" SELECT OPP.PAYMENTS_ID,OPP.BILL_CODE, OPP.`NAME`,CONCAT(LEFT (ID_NUMBER, 3),'****',RIGHT (ID_NUMBER, 3)) as ID_NUMBER, OPP.PERIODS ");
		sql.append(" ,OPP.PAYMENTS_DATE, ");
		sql.append(" OPP.RESIDUAL_PRINCIPAL, OPP.PAY_PRINCIPAL, OPP.PAY_INTEREST, ");
		sql.append(" OPP.PAY_PRINCIPAL-IFNULL((ORR.REPAYMENT_PRINCIPAL),0) AS SHOLD_PRINCIPAL, ");
		sql.append(" OPP.PAY_INTEREST-IFNULL((ORR.REPAYMENT_INTEREST),0) AS SHOLD_INTEREST, ");
		sql.append(" IFNULL(ORR.REPAYMENT_PENALTY,0) AS PENALTY, OPP.REPAYMENT_STATUS ");
		sql.append(" FROM O2O_PAYMENTS_PLAN OPP LEFT JOIN ");
		sql.append(" (SELECT `OR`.REPAYMENT_NO,SUM(`OR`.REPAYMENT_PRINCIPAL) REPAYMENT_PRINCIPAL ");
		sql.append(" ,SUM(`OR`.REPAYMENT_INTEREST) REPAYMENT_INTEREST ");
		sql.append(" ,SUM(`OR`.REPAYMENT_PENALTY) REPAYMENT_PENALTY ");
		sql.append(" FROM O2O_REPAYMENT_RECORD `OR` WHERE `OR`.IS_DELETE='N' GROUP BY `OR`.REPAYMENT_NO ) ORR ");
		sql.append(" ON OPP.PAYMENTS_ID=ORR.REPAYMENT_NO WHERE OPP.IS_DEL = 'N' ");

		// 单据编号
		if (vo.getBillCode() != null && !"".equals(vo.getBillCode())) {
			sql.append(" AND OPP.BILL_CODE = ?");
			sqlPageQuery.addParam(vo.getBillCode());
		}
		// 主键
		if (vo.getPayments_id() != null && !"".equals(vo.getPayments_id())) {
			sql.append(" AND OPP.PAYMENTS_ID = ?");
			sqlPageQuery.addParam(vo.getPayments_id());
		}
		// 计划还款日期 begin
		if (vo.getBeginPaymentsDate() != null && !"".equals(vo.getBeginPaymentsDate())) {
			sql.append(" AND OPP.PAYMENTS_DATE>= ?");
			try {
				sqlPageQuery.addParam(s.parse(vo.getBeginPaymentsDate()));
			} catch (ParseException e) {
				e.printStackTrace();
			}
		}
		// 计划还款日期 end
		if (vo.getEndPaymentsDate() != null && !"".equals(vo.getEndPaymentsDate())) {
			sql.append(" AND OPP.PAYMENTS_DATE<= ?");
			try {
				sqlPageQuery.addParam(s.parse(vo.getEndPaymentsDate()));
			} catch (ParseException e) {
				e.printStackTrace();
			}
		}
		if(StringUtil.isNotBlank(vo.getPayType())){
			sql.append(" AND OPP.pay_type = ? ");
			sqlPageQuery.addParam(vo.getPayType());
		}else{
			sql.append(" AND ( OPP.pay_type is null or OPP.pay_type ='' ) ");
		}
		sql.append(" ORDER BY OPP.PERIODS ASC");
		sqlPageQuery.setQuerySql(sql.toString());
		PageResult<Object[]> loanBasepage = paymentsDao.findDataWithPage(sqlPageQuery);
		return loanBasepage;
	}

	@Override
	public String savePayments(O2oPaymentsPlan vo) {

		Date date = new Date();
		vo.setOprateTime(date);
		User user = UserHolder.getUser();
		if (user != null) {
			vo.setOpratePsn(UserHolder.getUser().getCname());
		}
		vo.setIsDel("N");
		boolean flag = paymentsDao.insert(vo) >= 1 ? true : false;
		if (flag == true) {
			return vo.getPaymentsId();
		} else {
			return null;
		}
	}

	@Override
	public boolean delPayments(String payments_id) {

		int flag = paymentsDao.execute("update o2o_payments_plan t set t.is_del ='Y' where t.payments_id =? ", payments_id);
		return flag > 0 ? true : false;
	}
	
	public boolean delNewPayments(String payments_id) {
		
		int flag = paymentsDao.execute("update o2o_ea_loan_data_td t set t.is_del ='Y' where t.id =? ", payments_id);
		return flag > 0 ? true : false;
	}

	@Override
	public List<O2oLoanapplyEntity> findO2oLoanapplyEntity(String loan_id) {
		StringBuilder sql = new StringBuilder();
		List<Object> listParams = new ArrayList<Object>();
		sql.append("select * from o2o_loanapply where 1=1 and wf_type in('SF','O') ");
		if (StringUtil.isNotBlank(loan_id)) {

			sql.append(" and loan_id = ?");
			listParams.add(loan_id);
		}

		return paymentsDao.findEntitys(sql.toString(), O2oLoanapplyEntity.class, listParams.toArray());
	}

	@Override
	public List<O2oLoanapplyEntity> findO2oLoanapplyEntityByName(String name) {
		StringBuilder sql = new StringBuilder();
		List<Object> listParams = new ArrayList<Object>();
		sql.append("select * from o2o_loanapply where 1=1 and wf_type in('SF','O') and loan_id IN ");
		sql.append(" (select id from o2o_loan_user  where user_full_name= ? )");
		listParams.add(name);
		return paymentsDao.findEntitys(sql.toString(), O2oLoanapplyEntity.class, listParams.toArray());
	}

	@Override
	public List<O2oPaymentsPlan> queryBillCode(String code) {
		String sql = "select * from o2o_payments_plan t where t.bill_code=?";
		return paymentsDao.findEntitys(sql, O2oPaymentsPlan.class, code);
	}
	
	/**
	 * @Description: <p> 根据贷款编号查询还款计划最后一期的数据  </p>
	 * @author CXW
	 * @date 2016年9月18日 上午9:50:23
	 * @version V1.0
	 * @param 
	 * @return O2oPaymentsPlan
	 */
	public O2oPaymentsPlan findByBillCode(String billCode) {
		StringBuffer sql = new StringBuffer();
		sql.append("select t.payments_id,t.bill_code,t.data_type,t.id_number,t.is_del,t.`name`");
		sql.append(",max(t.payments_date) payments_date,t.pay_interest,t.oprate_psn,t.oprate_time");
		sql.append(",t.pay_principal,t.pay_total,t.periods,t.repayment_status,t.residual_principal");
		sql.append(" from o2o_payments_plan t where t.bill_code=? and t.is_del = 'N' GROUP BY t.bill_code");
		return paymentsDao.findEntity(sql.toString(), O2oPaymentsPlan.class, billCode);
	}

	/**
	 * @Description:
	 * 				<p>
	 *               保存还款记录
	 *               </p>
	 * @author CXW
	 * @date 2016年8月29日 下午6:23:09
	 * @version V1.0
	 * @param o2oRepaymentEntity
	 * @return int
	 */
	@Transactional(transactionManager = "o2oTransactionManager")
	public int insertO2oRepaymentEntity(O2oRepaymentEntity o2oRepaymentEntity) {
		return paymentsDao.insert(o2oRepaymentEntity);
	}

	/**
	 * @Description:
	 * 				<p>
	 *               更新还款记录
	 *               </p>
	 * @author CXW
	 * @date 2016年8月29日 下午6:23:09
	 * @version V1.0
	 * @param o2oRepaymentEntity
	 * @return int
	 */
	@Transactional(transactionManager = "o2oTransactionManager")
	public int updateO2oRepaymentEntity(O2oRepaymentEntity o2oRepaymentEntity, O2oPaymentsPlan o2oPaymentsPlan) {
		paymentsDao.updateById(o2oPaymentsPlan);
		return paymentsDao.updateById(o2oRepaymentEntity);
	}

	@Transactional(transactionManager = "o2oTransactionManager")
	public int updateO2oPaymentsPlanEntity(O2oPaymentsPlan o2oPaymentsPlan) {
		return paymentsDao.updateById(o2oPaymentsPlan);
	}

	public O2oRepaymentEntity findEntityById(String id) {
		String sql = "SELECT ORR.* FROM O2O_REPAYMENT_RECORD ORR WHERE ORR.IS_DELETE='N' AND ORR.ID = ? ";
		return paymentsDao.findEntity(sql, O2oRepaymentEntity.class, id);
	}

	public O2oRepaymentRecordFindVo findByParam(String repaymentNo) {
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT OPP.PAY_PRINCIPAL-IFNULL((ORR.REPAYMENT_PRINCIPAL),0) AS REPAYMENT_PRINCIPAL,");
		sql.append("OPP.PAY_INTEREST-IFNULL((ORR.REPAYMENT_INTEREST),0) AS REPAYMENT_INTEREST ");
		sql.append("FROM O2O_PAYMENTS_PLAN OPP LEFT JOIN ");
		sql.append("(SELECT `OR`.REPAYMENT_NO,SUM(`OR`.REPAYMENT_PRINCIPAL) REPAYMENT_PRINCIPAL ");
		sql.append(",SUM(`OR`.REPAYMENT_INTEREST) REPAYMENT_INTEREST ");
		sql.append(",SUM(`OR`.REPAYMENT_PENALTY) REPAYMENT_PENALTY ");
		sql.append("FROM O2O_REPAYMENT_RECORD `OR` WHERE `OR`.IS_DELETE='N' GROUP BY `OR`.REPAYMENT_NO ) ORR ");
		sql.append("ON OPP.PAYMENTS_ID=ORR.REPAYMENT_NO ");
		sql.append("WHERE OPP.IS_DEL = 'N' AND OPP.PAYMENTS_ID = ? ");
		return paymentsDao.findEntity(sql.toString(), O2oRepaymentRecordFindVo.class, repaymentNo);
	}

	public O2oRepaymentEntity findEntityByRepaymentNo(String repaymentNo) {
		String sql = "SELECT OP.* FROM O2O_REPAYMENT_RECORD OP WHERE OP.IS_DELETE='N' AND OP.REPAYMENT_NO = ?";
		return paymentsDao.findEntity(sql, O2oRepaymentEntity.class, repaymentNo);
	}

	public O2oPaymentsPlan findPaymentsPlanEntityById(String id) {
		String sql = "SELECT OP.* FROM O2O_PAYMENTS_PLAN OP WHERE OP.PAYMENTS_ID = ?";
		return paymentsDao.findEntity(sql, O2oPaymentsPlan.class, id);
	}

	/**
	 * @Description:
	 * 				<p>
	 *               查询还款记录
	 *               </p>
	 * @author CXW
	 * @date 2016年8月29日 下午6:40:02
	 * @version V1.0
	 * @param
	 * @return PageResult<Object[]>
	 */
	@Override
	public PageResult<Object[]> repaymentsList(O2oPaymentsForm o2oPaymentsForm) {
		SqlPageQuery<Object[]> sqlPageQuery = new SqlPageQuery<>(Object[].class, o2oPaymentsForm.getPageSize(), o2oPaymentsForm.getShowPage());
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT ORR.ID,ORR.PERIODS,OPP.PAYMENTS_DATE,ORR.ACTUAL_REPAYMENT_DATE,ORR.REPAYMENT_PRINCIPAL");
		sql.append(",ORR.REPAYMENT_INTEREST,ORR.REPAYMENT_PENALTY,od.original_name,ORR.BILL_STATUS,od.datalist_no ");
		sql.append("FROM O2O_REPAYMENT_RECORD ORR join O2O_PAYMENTS_PLAN OPP on OPP.BILL_CODE = ORR.BILL_CODE AND ");
		sql.append("OPP.PAYMENTS_ID = ORR.REPAYMENT_NO left join o2o_datalist od on od.payments_id = ORR.id ");
		sql.append("WHERE ORR.IS_DELETE='N' AND ORR.BILL_CODE = ? ");
		sqlPageQuery.addParam(o2oPaymentsForm.getBillCode());
		if (!StringUtil.isEmpty(o2oPaymentsForm.getPayments_id())) {
			sql.append(" AND ORR.REPAYMENT_NO = ? ");
			sqlPageQuery.addParam(o2oPaymentsForm.getPayments_id());
		}
		sql.append(" ORDER BY ORR.UPDATE_TIME DESC ");
		sqlPageQuery.setQuerySql(sql.toString());
		PageResult<Object[]> loanBasepage = paymentsDao.findDataWithPage(sqlPageQuery);
		return loanBasepage;
	}
	
	/**
	 * @Description:
	 *               <p>
	 *                NEW 查询还款记录
	 *               </p>
	 * @author CXW
	 * @date 2016年12月12日 上午11:09:54
	 * @version V1.0
	 */
	public PageResult<Object[]> newRepaymentsList(O2oPaymentsForm o2oPaymentsForm) {
		SqlPageQuery<Object[]> sqlPageQuery = new SqlPageQuery<>(Object[].class, o2oPaymentsForm.getPageSize(), o2oPaymentsForm.getShowPage());
		StringBuilder sql = new StringBuilder();
		sql.append(" SELECT OBR.ID,DATE_FORMAT(OBR.NEW_TIME,'%Y-%m-%d') AS NEW_TIME,");
		sql.append(" OBR.THIS_PAYMENT_PRINCIPAL,");
		sql.append(" DATE_FORMAT(OBR.THIS_PAYMENT_DATE,'%Y-%m-%d') AS THIS_PAYMENT_DATE,");
		sql.append(" OD.DATALIST_NO,OBR.FLOW_STATUS");
		sql.append(" FROM O2O_BADLOAN_RECORD AS OBR LEFT JOIN O2O_DATALIST OD");
		sql.append(" ON OBR.ID = OD.LOANAPPLY_NO AND OD.IS_DELETE='N' WHERE OBR.BILL_CODE = ?");
		sqlPageQuery.addParam(o2oPaymentsForm.getBillCode());
		sql.append(" ORDER BY OBR.NEW_TIME ASC ");
		sqlPageQuery.setQuerySql(sql.toString());
		PageResult<Object[]> loanBasepage = paymentsDao.findDataWithPage(sqlPageQuery);
		return loanBasepage;
	}

	/**
	 * 启动回款流程
	 */
	public ModelResult<Boolean> startPayBack(O2oRepaymentEntity o2oRepaymentEntity) { // 根据单号去启动流程
		O2oLoanapplyEntity loan = o2oLoanapplyService.findById(o2oRepaymentEntity.getLoanapply_no());
		o2oRepaymentEntity = findEntityById(o2oRepaymentEntity.getId());
		O2oPaymentsPlan o2oPaymentsPlan = findPaymentsPlanEntityById(o2oRepaymentEntity.getRepayment_no());
		// 获取 o2opay对象
		WFRequestParam param = new WFRequestParam();
		param.setReferId(o2oRepaymentEntity.getId());
		param.setReferCode(loan.getBillCode());
		// 回款流程的url
		param.setReferUrl(startUrl + "?billCode=" + loan.getBillCode() + "&paymentsId=" + o2oPaymentsPlan.getPeriods());
		param.setSystemModule(ProcessTemplate.SYSTEM_MODULE_O2O);
		param.setProcessType(ProcessTemplate.PROECESS_TYPE_BACK_MONEY); // 修改成回款流程编号
		User user = UserHolder.getUser();
		param.setAppliedby(user.getNo());
		param.setAppliedbyName(user.getCname());
		param.setOrgNo(loan.getLtd());
		StringBuffer appliedDes = new StringBuffer("借款人:");
		appliedDes.append(o2oLoanapplyService.findLoanUserEntity(loan.getLoanapplyNo(), loan.getLoanId()).getUserFullName());
		appliedDes.append("，放款金额:");
		java.text.DecimalFormat df = new java.text.DecimalFormat("0.00"); // 格式化，保留两位小数
		BigDecimal loanAmount=new BigDecimal(0);
		if ("EA".equals(loan.getLoanType())||"FQ".equals(loan.getLoanType())) {
			loanAmount = service.findDataByBillCode(loan.getBillCode()).getLoanAmount();
		}else{
			loanAmount = o2oLoanService.queryBillCode(loan.getBillCode()).getLoanAmount();
		}
		appliedDes.append(df.format(loanAmount));
		param.setAppliedDes(appliedDes.toString());
		log.debug("启动回款流程，参数有贷款id：【{}】，贷款编号：【{}】，系统模块：【{}】，流程类型：【{}】，经营平台id：【{}】，贷款摘要信息：【{}】", o2oPaymentsPlan.getPaymentsId(), loan.getBillCode(), ProcessTemplate.SYSTEM_MODULE_O2O, param.getProcessType(), loan.getLtd(), appliedDes.toString());
		ModelResult<Boolean> modelResult = workFlowService.instance(param);
		return modelResult;
	}

	@Override
	public O2oPaymentsPlan findByIdEntity(String id) {
		return paymentsDao.getById(O2oPaymentsPlan.class, id);
	}

	@Override
	public O2oRepaymentEntity findByBillCodeAndStatus(String billCode, String status) {
		String sql = "select orr.* from o2o_repayment_record orr where orr.bill_code=? and orr.bill_status=?";
		return paymentsDao.findEntity(sql, O2oRepaymentEntity.class, billCode, status);
	}

	@Transactional(transactionManager = "o2oTransactionManager")
	public int updateBySQL(String sql, String repayment_id, String status) {
		return paymentsDao.execute(sql, repayment_id, status);
	}

	public int updateO2oRepaymentEntity(O2oRepaymentEntity o2oRepaymentEntity) {
		return paymentsDao.updateById(o2oRepaymentEntity);
	}

	@Override
	public int findCount(String billCode, String status) {
		String sql = "select count(1) from o2o_payments_plan t where t.bill_code=? and t.repayment_status=?";
		return paymentsDao.findInteger(sql, billCode, status);
	}
	
	/**
	 * @Description: <p> 查询单条还款记录信息 </p>
	 * @author CXW
	 * @date 2016年9月7日 下午4:45:48
	 * @version V1.0
	 * @param 
	 * @return O2oRepaymentEntity
	 */
	public O2oRepaymentEntity findRecordById(String id){
		return paymentsDao.getById(O2oRepaymentEntity.class, id);
	}
	
	/**
	 * @Description: <p> check删除还款计划 </p>
	 * @author CXW
	 * @date 2016年9月7日 下午5:34:59
	 * @version V1.0
	 * @param sql,id
	 * @return int
	 */
	public int checkDeleteSpan(String sql, String id){
		return paymentsDao.findInteger(sql, id);
	}
	/**
	 * @Description: <p> 查询金额 </p>
	 * @author CXW
	 * @date 2016年9月7日 下午5:34:59
	 * @version V1.0
	 * @param sql,id
	 * @return int
	 */
	public String findBigDecimal(String sql, String id){
		return paymentsDao.findString(sql, id);
	}
}
